package db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import util.Number;
import model.City;
import model.Country;
import model.Customer;
import model.Project;
import model.SubContractor;

public class DBProject implements IFProject
{
	private Connection con;

	public DBProject()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public int createProject(Project project, ArrayList<SubContractor> subs, ArrayList<Double> price) throws SQLException
	{
		int rc = -1;

		String query = "SELECT service_name FROM service WHERE service_name = ?";

		if(DBConnection.doDebug())
			DBConnection.printDebug("createProject", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, project.getName());

			ResultSet results = ps.executeQuery();

			if(!results.next())
			{
				query = "INSERT INTO service(service_drawing_number, service_name, service_prod_quantity, service_description, service_dimensions, service_deadline, service_customer_id, service_type, service_active) VALUES (?, ?, ?, ?, ?, ?, ?, '2', '1');";

				if(DBConnection.doDebug())
					DBConnection.printDebug("createProject", query);

				try
				{
					ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

					ps.setInt(1, project.getDrawingNo());
					ps.setString(2, project.getName());
					ps.setInt(3, project.getProdQuantity());
					ps.setString(4, project.getDescription());
					ps.setString(5, project.getDimensions());
					ps.setString(6, project.getDeadline());
					ps.setInt(7,  project.getCustomer().getPersonID());

					rc = ps.executeUpdate();

					ResultSet insertedRow = ps.getGeneratedKeys();

					if((rc > 0) && insertedRow.next())
					{
						query = "" +
								"INSERT INTO offer(offer_price, offer_service_id) VALUES (?, ?);" +
								"INSERT INTO project(project_id, project_item_price, project_comments, project_link, project_offer_id) "
								+ "VALUES (?, ?, ?, ?, ?);";

						try
						{
							project.setServiceID(insertedRow.getInt(1));
							
							ps = con.prepareStatement(query);


							ps.setDouble(1, project.getPrice());
							ps.setInt(2, insertedRow.getInt(1));

							ps.setInt(3, insertedRow.getInt(1));
							ps.setDouble(4, project.getItemPrice());
							ps.setString(5, project.getComments());
							ps.setString(6, project.getProjLink());
							ps.setInt(7, insertedRow.getInt(1));

							rc = ps.executeUpdate();
							
							IFProjectSubContractor dbPS = new DBProjectSubContractor();
							
							int subPrice = 0;
							
							for(SubContractor sub : subs)
								{
									
							dbPS.addProjectSubContractor(project, sub, price.get(subPrice));
							
							subPrice++;
								}
						}
						catch(Exception e)
						{
							if (DBConnection.doDebug())
							{
								System.out.println("[ERROR] Query exception - createProject - INSERT #1: " + e);
								e.printStackTrace();
							}
						}
					}
				}
				catch(Exception e)
				{
					if (DBConnection.doDebug())
					{
						System.out.println("[ERROR] Query exception - createProject - INSERT #2: " + e);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch(Exception e)
		{
			if (DBConnection.doDebug())
			{
				System.out.println("[ERROR] Query exception - createProject - SELECT: " + e);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int transferProject(int offerID,Project project, ArrayList<SubContractor> subs, ArrayList<Double> price) throws SQLException 
	{
		System.out.println("TEST : ");
		int rc = -1;

		String query = "INSERT INTO service(service_drawing_number, service_name, service_prod_quantity, service_description, service_dimensions, service_deadline, service_customer_id, service_type, service_active) VALUES (?, ?, ?, ?, ?, ?, ?, '2', '1');";

		if(DBConnection.doDebug())
			DBConnection.printDebug("transferProject", query);
		System.out.println("PRICE IS : ");
		try
		{
			PreparedStatement ps = con.prepareStatement(query, PreparedStatement.RETURN_GENERATED_KEYS);

			ps.setInt(1, project.getDrawingNo());
			ps.setString(2, project.getName());
			ps.setInt(3, project.getProdQuantity());
			ps.setString(4, project.getDescription());
			ps.setString(5, project.getDimensions());
			ps.setString(6, project.getDeadline());
			ps.setInt(7, project.getCustomer().getPersonID());

			rc = ps.executeUpdate();

			ResultSet insertedRow = ps.getGeneratedKeys();

			if((rc > 0) && insertedRow.next())
			{

				query = "INSERT INTO project(project_id,project_item_price, project_comments, project_link, project_offer_id) VALUES (?,?, ?, ?, ?);";

				try
				{
					project.setServiceID(insertedRow.getInt(1));
					
					ps = con.prepareStatement(query);
					ps.setInt(1,  insertedRow.getInt(1));
					ps.setDouble(2, 0);
					ps.setString(3, project.getComments());
					ps.setString(4, project.getProjLink());
					ps.setInt(5, offerID);
					for (int i = 0 ; i < price.size(); i++)
						{
					System.out.println("PRICE IS : " + price.get(i));
					System.out.println("ID IS : " + project.getServiceID());
					System.out.println("OUTSIDER ID IS : " + subs.get(i).getOutsiderID());
						}
					
					rc = ps.executeUpdate();
					
					IFProjectSubContractor dbPS = new DBProjectSubContractor();
					
					int subPrice = 0;
					
					
					for(SubContractor sub : subs)
						{
							
					dbPS.addProjectSubContractor(project, sub, price.get(subPrice));
					
					subPrice++;
						}

					
				}
				catch(Exception e)
				{
					if(DBConnection.doDebug())
					{
						DBConnection.printDebug("transferProject", query);
						e.printStackTrace();
					}
				}
			}

			ps.close();
		}
		catch(Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("transferProject", query);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateProject(Project project)
	{
		int rc = -1;

		String query = "" +
				"UPDATE service SET service_drawing_number = ?, service_name = ?, service_prod_quantity = ?, service_description = ?, service_dimensions = ?, service_deadline = ?, service_customer_id = ? WHERE service_id = ?; " +
				"UPDATE project SET project_comments = ?, project_link = ? WHERE project_id = ?;" +
				"UPDATE offer SET offer_price = ? WHERE offer_service_id = ?;";

		if(DBConnection.doDebug())
			DBConnection.printDebug("updateProject", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, project.getDrawingNo());
			ps.setString(2, project.getName());
			ps.setInt(3, project.getProdQuantity());
			ps.setString(4, project.getDescription());
			ps.setString(5, project.getDimensions());
			ps.setString(6, project.getDeadline());
			ps.setInt(7, project.getCustomer().getPersonID());
			ps.setInt(8, project.getServiceID());

			ps.setString(9, project.getComments());
			ps.setString(10, project.getProjLink());
			ps.setInt(11, project.getServiceID());
			ps.setDouble(12, project.getPrice());
			ps.setInt(13, project.getServiceID());

			rc = ps.executeUpdate();

			ps.close();

		}
		catch(Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("updateProject", query);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int activateProject(int projectID)
	{
		int rc = -1;

		String query = "UPDATE service SET service_active = CASE service_active WHEN 0 THEN 1 ELSE 0 END WHERE service_id = ?";

		if(DBConnection.doDebug())
			DBConnection.printDebug("activateProject", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, projectID);

			rc = ps.executeUpdate();

			ps.close();
		}
		catch(Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("activateProject", query);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public int updateProjectPrice(int projectID) throws SQLException
	{
		int rc = -1;

		String query = "UPDATE project SET project_item_price = (SELECT SUM(pui.pui_quantity*i.item_price) FROM project_used_item AS pui LEFT JOIN item AS i ON pui.pui_item_id = i.item_id WHERE pui.pui_project_id = project_id) WHERE project_id = ?";

		if(DBConnection.doDebug())
			DBConnection.printDebug("updateProjectPrice", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, projectID);

			rc = ps.executeUpdate();
		}
		catch (SQLException e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("updateProjectPrice", query);
				e.printStackTrace();
			}
		}

		return rc;
	}

	@Override
	public Project findProjectByName(String name) throws SQLException
	{
		String query = "SELECT p.*, s.*, o.offer_price, ps.person_username, ct.city_name, cn.country_name, (SELECT COUNT(pui_id) FROM project_used_item WHERE pui_project_id = p.project_id) AS item_count FROM project AS p LEFT JOIN service AS s ON s.service_id = p.project_id LEFT JOIN offer AS o ON o.offer_service_id = p.project_offer_id LEFT JOIN customer AS c ON c.customer_id = s.service_customer_id LEFT JOIN person as ps ON ps.person_id = c.customer_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE s.service_name = ?";
		Project project = new Project();

		if(DBConnection.doDebug())
			DBConnection.printDebug("findProjectByName", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, name);

			ResultSet results = ps.executeQuery();

			if (results.next())
			{
				project = buildProjectObject(results);

				project.setComments(results.getString("project_comments"));
				project.setProjLink(results.getString("project_link"));
				project.setItemPrice(results.getDouble("project_item_price"));
			}

			ps.close();
		}
		catch(Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("findProjectByName", query);
				e.printStackTrace();
			}
		}

		return project;
	}
	@Override
	public Project findProjectByID(int id) throws SQLException
	{
		String query = "SELECT p.*, s.*, o.offer_price, ps.person_username, ct.city_name, cn.country_name, (SELECT COUNT(pui_id) FROM project_used_item WHERE pui_project_id = p.project_id) AS item_count FROM project AS p LEFT JOIN service AS s ON s.service_id = p.project_id LEFT JOIN offer AS o ON o.offer_service_id = p.project_offer_id LEFT JOIN customer AS c ON c.customer_id = s.service_customer_id LEFT JOIN person as ps ON ps.person_id = c.customer_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE s.service_id = ?";
		Project project = new Project();

		if(DBConnection.doDebug())
			DBConnection.printDebug("findProjectByName", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setInt(1, id);

			ResultSet results = ps.executeQuery();

			if (results.next())
			{
				project = buildProjectObject(results);

				project.setComments(results.getString("project_comments"));
				project.setProjLink(results.getString("project_link"));
				project.setItemPrice(results.getDouble("project_item_price"));
			}

			ps.close();
		}
		catch(Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("findProjectByName", query);
				e.printStackTrace();
			}
		}

		return project;
	}

	@Override
	public ArrayList<Project> getAllActiveProjects() throws SQLException
	{
		ArrayList<Project> projectList = new ArrayList<Project>();

		String query = "SELECT service_name FROM service WHERE service_type = '2' AND service_active = '1' ORDER BY service_name ASC";

		if(DBConnection.doDebug())
			DBConnection.printDebug("getAllActiveProjects", query);

		try
		{
			Statement st = con.createStatement();
			ResultSet result = st.executeQuery(query);

			while (result.next()) 
			{
				Project newProject = new Project();

				newProject.setName(result.getString("service_name"));

				projectList.add(newProject);
			}

			st.close();
		}
		catch (Exception e)
		{
			if(DBConnection.doDebug())
			{
				DBConnection.printDebug("getAllActiveProjects", query);
				e.printStackTrace();
			}
		}

		return projectList;
	}

	@Override
	public ArrayList<Project> searchProjectByName(String name, boolean active) throws SQLException
	{
		return searchProjects(active, 1, name, "s.service_name LIKE ?");
	}

	@Override
	public ArrayList<Project> searchProjectByCity(String city, boolean active) throws SQLException
	{
		return searchProjects(active, 1, city, "ct.city_name LIKE ?");
	}

	@Override
	public ArrayList<Project> searchProjectByCountry(String country, boolean active) throws SQLException
	{
		return searchProjects(active, 1, country, "cn.country_name LIKE ?");
	}

	@Override
	public ArrayList<Project> searchProjectByCustomer(String customer, boolean active) throws SQLException
	{
		return searchProjects(active, 1, customer, "ps.person_username LIKE ?");
	}
	
	@Override
	public ArrayList<Project> searchProjectByEverything(String search, boolean active) throws SQLException
	{
		boolean number = Number.isNumber(search);

		return searchProjects(active, (number ? 5 : 4), search, "(s.service_name LIKE ? OR ps.person_username LIKE ? OR ct.city_name LIKE ? OR cn.country_name LIKE ?" + (number ? " OR s.service_id = ?" : "") + ")");
	}

	private ArrayList<Project> searchProjects(boolean active, int num, String search, String clause)
	{
		ArrayList<Project> projectList = new ArrayList<Project>();

		String query = "SELECT s.*,p.*, o.offer_price, ps.person_username, ct.city_name, cn.country_name FROM project AS p LEFT JOIN service AS s ON s.service_id = p.project_id LEFT JOIN offer AS o ON o.offer_service_id = p.project_offer_id LEFT JOIN customer AS c ON c.customer_id = s.service_customer_id LEFT JOIN person as ps ON ps.person_id = c.customer_id LEFT JOIN city AS ct ON c.customer_city_id = ct.city_id LEFT JOIN country AS cn ON ct.city_country_id = cn.country_id WHERE " + clause + (active ? " AND s.service_active = 1" : "");

		if(DBConnection.doDebug())
			DBConnection.printDebug("searchProjects", query);

		try
		{
			PreparedStatement ps = con.prepareStatement(query);

			ps.setString(1, "%" + search + "%");

			if (num > 1)
			{
				for(int i = 2; i <= num; i++)
					ps.setString(i, (i == 6 ? search : "%" + search + "%"));						
			}

			ResultSet results = ps.executeQuery();

			while (results.next())
			{
				Project newProject = buildProjectObject(results);

				projectList.add(newProject);
			}
		}
		catch (Exception e)
		{
			e.printStackTrace();
		}

		return projectList;
	}

	private Project buildProjectObject(ResultSet results) throws SQLException
	{
		Project project = new Project();

		// service part
		project.setServiceID(results.getInt("service_id"));
		project.setDrawingNo(results.getInt("service_drawing_number"));
		project.setName(results.getString("service_name"));
		project.setProdQuantity(results.getInt("service_prod_quantity"));
		project.setDimensions(results.getString("service_dimensions"));
		project.setDescription(results.getString("service_description"));
		project.setDeadline(results.getString("service_deadline"));
		project.setCustomer(new Customer(results.getInt("service_customer_id"), results.getString("person_username"), new City(results.getString("city_name"), new Country(results.getString("country_name")))));

		project.setPrice(results.getDouble("offer_price"));
		
		return project;
	}
}